package com.softer.chengxin.corp.dao;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.corp.dao.provider.CorpDaoProvider;
import com.softer.chengxin.corp.domin.ApproveDTO;
import com.softer.chengxin.corp.domin.Corp;
import com.softer.chengxin.corp.domin.CorpVO;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author Gong Zhiyang
 * @date 2018/10/8
 */
@Mapper
@Repository
public interface CorpDao {

    /**
     * 根据用户名查询diccode
     * @param loginName
     * @return
     */
    @Select("SELECT dic_code FROM roules r LEFT JOIN manager_user m on r.id = m.role_id WHERE m.login_name = #{name}")
    String getDicCodeByLoginName(@Param("name") String loginName);

    /**
     * 查询企业基本信息
     *
     * @param id 企业ID
     * @return 企业基本信息
     */
    @Select("SELECT id, DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate, DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified, qy_no AS qyNo,main_business_income as mainBusinessIncome, url, contacts," +
            "name, legal_person AS legalPerson, general_manager AS generalManager, business_date AS businessDate,public_income as publicIncome,fax, email, phone," +
            "register_type AS registerTypeCode,(SELECT name FROM dic_info WHERE code = register_type) AS registerType, province, city, district, street, community, business_license_no AS businessLicenseNo,profit,main_scope AS mainScope," +
            "expiration_begin AS expirationBeginTime,expiration_end AS expirationEndTime, system_type AS systemTypeCode,(SELECT name FROM dic_info WHERE code = system_type) AS systemType, registered_capital AS registeredCapital,other_income as otherIncome," +
            " sideline AS sideline, post_code AS postCode, (SELECT opinion FROM company_info_opinion WHERE company_id=#{id} ORDER BY gmt_create DESC limit 1) as opinion," +
            "register_address AS registerAddress,public_number AS publicNumber,first_contract_path as firstContractPath,state as state,organization,three_system as threeSystem," +
            "credit_contract_path as creditContractPath,business_license_path as businessLicensePath,first_contract_date as firstContractDate,province_company as provinceCompany," +
            "company_type as companyType,enterprise_qualification as enterpriseQualification," +
            "qualification_approval_date as qualificationApprovalTime," +
            "qualification_certificate_no as qualificationCertificateNo," +
            "qualification_expiration_date as qualificationExpirationTime," +
            "approval_authority as approvalAuthority,national_hundred as nationalHundred," +
            "famous_brand as famousBrand FROM company_base_info WHERE id = #{id}")
    Corp getCorpInfo(@Param("id") Integer id);

    /**
     * 更新企业状态
     *
     * @param id    企业ID
     * @param state 状态
     * @return 处理结果
     */
    @Update("UPDATE company_base_info SET state=#{state},gmt_modified=now() WHERE id=#{id}")
    int updateState(@Param("id") Integer id, @Param("state") Integer state);

    /**
     * 更新企业信息
     *
     * @param corp 企业信息
     * @return 处理结果
     */
    @Update("UPDATE company_base_info SET gmt_modified = NOW(), `name` =#{corp.name}, legal_person =#{corp.legalPerson}, general_manager =#{corp.generalManager}, business_date =#{corp.businessTime},register_type =#{corp.registerType},main_business_income=#{corp.mainBusinessIncome}, three_system =#{corp.threeSystem} ," +
            "province=#{corp.province},city=#{corp.city},district=#{corp.district},street=#{corp.street},business_license_no=#{corp.businessLicenseNo},expiration_begin=#{corp.expirationBeginTime},expiration_end=#{corp.expirationEndTime},system_type=#{corp.systemType},public_income=#{corp.publicIncome},profit=#{corp.profit}," +
            "registered_capital=#{corp.registeredCapital},main_scope=#{corp.mainScope},sideline=#{corp.sideline},fax=#{corp.fax},post_code=#{corp.postCode}, url=#{corp.url}, contacts=#{corp.contacts},email=#{corp.email},phone=#{corp.phone},other_income=#{corp.otherIncome},organization=#{corp.organization}, " +
            "register_address=#{corp.registerAddress},public_number=#{corp.publicNumber},first_contract_date=#{corp.firstContractTime},credit_contract_path=#{corp.creditContractPath},first_contract_path=#{corp.firstContractPath},business_license_path=#{corp.businessLicensePath},province_company=#{corp.provinceCompany},company_type=#{corp.companyType}," +
            "enterprise_qualification=#{corp.enterpriseQualification},qualification_approval_date=#{corp.qualificationApprovalTime},qualification_certificate_no=#{corp.qualificationCertificateNo},qualification_expiration_date=#{corp.qualificationExpirationTime},approval_authority=#{corp.approvalAuthority},national_hundred=#{corp.nationalHundred},famous_brand=#{corp.famousBrand} WHERE id=#{corp.id}")
    int updateCorpInfo(@Param("corp") Corp corp);

    /**
     * 根据登陆名，获取相应的管理区域
     *
     * @param loginName 登陆名
     * @return 管理地区ID（用","分割的集合）
     */
    @Select("SELECT area FROM manager_user WHERE login_name=#{name}")
    String getDistinct(@Param("name") String loginName);

    /**
     * 查找企业当前状态
     * @param loginName 登陆账号
     * @return
     */
    @Select("SELECT state FROM `company_base_info` WHERE qy_no=#{name}")
    Integer queryState(@Param("name") String loginName);

    /**
     * 获取管理下的企业列表
     *
     * @param page  页面实体
     * @param area  管辖区域
     * @param state 状态
     * @param name  企业名称
     * @return 企业集合
     */
    @SelectProvider(type = CorpDaoProvider.class, method = "getCorpList")
    List<Corp> getCorpList(@Param("page") Page page, @Param("area") String area, @Param("state") String state, @Param("name") String name,@Param("provinceCompany") String provinceCompany);

    /**
     * 获取管理下的企业信息数量
     *
     * @param area  管辖区域
     * @param state 状态
     * @param name  企业名称
     * @return 企业数量
     */
    @SelectProvider(type = CorpDaoProvider.class, method = "getCorpListNum")
    int getCorpListNum(@Param("area") String area, @Param("state") String state, @Param("name") String name,@Param("provinceCompany") String provinceCompany);

    /**
     * 新增一条企业审批记录
     *
     * @param approveDTO 审批实体
     * @param loginName 审批人
     * @return 新增结果
     */
    @Insert("INSERT INTO company_info_opinion(gmt_create,gmt_modified,company_id,opinion,state,adder) VALUES(now(),now(),#{a.ID},#{a.yijian},#{a.state},#{loginName})")
    int addCorpOpinion(@Param("a") ApproveDTO approveDTO,@Param("loginName") String loginName);

    /**
     * 新增一条项目审批记录
     *
     * @param approveDTO 审批实体
     * @param loginName 审批人
     * @return 新增结果
     */
    @Insert("INSERT INTO company_project_opinion(gmt_create,gmt_modified,project_id,opinion,state,adder) VALUES(now(),now(),#{a.ID},#{a.yijian},#{a.state},#{loginName})")
    int addProjectOpinion(@Param("a") ApproveDTO approveDTO,@Param("loginName") String loginName);

    /**
     * 查询企业提交信息页面
     * @param id 企业id
     * @return 信息
     */
    @Select("SELECT id, `name`,(SELECT adder FROM company_info_opinion WHERE company_id=#{id} ORDER BY gmt_create DESC limit 1) as adder,DATE_FORMAT(gmt_create, '%Y-%m-%d') as gmtCreate,state  FROM  company_base_info WHERE id=#{id}")
    List<Map> querySubmitMsg(@Param("id") Integer id);

    /**
     * 查询该企业的固定人员人数
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select count(*) from company_person where company_id=#{id} and state=1 and insurance=1 ")
    int findCountPerson(@Param("id") Integer corpId);

    /**
     * 查询该企业的管理人员  有中级职称以上人数
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select count(*) from company_person where company_id=#{id} and certificate=1 and state=1")
    int findCountManagerPerson(@Param("id") Integer corpId);

    /**
     * 查询该企业的项目负责人人数
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select count(*) from company_person " +
            "where company_id=#{id} and state=1 and duties=10806")
    int getCountProManagerPerson(@Param("id") Integer corpId);

    /**
     * 查询该企业的参加培训的项目负责人人数
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select count(*) from company_person p " +
            "where p.id in (select person_id from company_train GROUP BY person_id HAVING YEAR(gmt_create)=#{year}) " +
            "and p.company_id=#{id} and p.state=1 and p.duties=10806")
    int getCountProManagerPersonTrain(@Param("id") Integer corpId,
                                      @Param("year")String year);

    /**
     * 查询该企业的参加培训的普通员工人数
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select count(*) from company_person p " +
            "where p.id in(select person_id from company_train GROUP BY person_id HAVING YEAR(gmt_create)=#{year}) " +
            "and p.company_id=#{id} and p.state=1 and p.duties=10807")
    int getCountProPersonTrain(@Param("id") Integer corpId,
                               @Param("year")String year);

    /**
     * 获取企业信息
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select id ,credit_contract_path as creditContractPath," +
            "year(first_contract_date) as `year` from company_base_info " +
            "where id=#{id}")
    CorpVO getCorpOperate(@Param("id") Integer corpId);

    /**
     * 获取企业物业管理面积信息
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select sum(all_area) from company_project " +
            "where company_id=#{id} and state=2")
    Integer getCorpArea(@Param("id") Integer corpId);

    /**
     * 获取企业保障用房或老旧小区物业管理面积信息
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select sum(all_area) from company_project " +
            "where state=2 and is_indemnificatory=1 or is_old=1 and company_id=#{id}")
    Integer getCorpBZArea(@Param("id") Integer corpId);



}
